package procedure;

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.UUID;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;
import org.w3c.dom.Element;



/**
 * A derby database implementation of the Storage interface using JDBC two phase
 * commit. All strings that are inserted into the database must be encoded using
 * org.apache.commons.codec.net.BCodec which is an implementation of the Base64
 * algorithm for strings. Strings are automatically decoded upon retrieval from
 * the database. All storage routines are transactional and can be embedded into
 * bigger transactions.
 * 
 * ** RENAMING THIS CLASS MAY BREAK SUBSTANTIAL STORED PROCEDURES!
 * 
 * @author Yudi Xue
 * 
 */
public class RemoteDerbyStorage implements 
		DerbySQLCreateTableStringsI, DerbySQLDropTableStringsI,
		DerbySQLProcedureStringsI, SQLModelProjectStringsI,
		SQLModelVersionStringsI, SQLScenarioCollectionStringsI,
		SQLScenarioStringsI, SQLParameterSetStringsI, SQLParameterStringsI,
		DerbySQLFileStringsI, SQLUserStringsI, SQLGroupStringsI {

	/**
	 * Remote procedure of select user (not to be used in other means).
	 * 
	 * @param data1
	 *            - the array to be returned
	 * @throws SQLException
	 */
	public static void selectUserProcedure(ResultSet[] data1)
			throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		PreparedStatement ps1 = conn.prepareStatement(USER_SELECT_ALLIDS);
		data1[0] = ps1.executeQuery();
		conn.close();
	}


	// ///////////////////////////////////////////////////////////////////////////////
	// Derby Procedures
	//
	// No explicit transaction control in derby proceture call
	// @see http://db.apache.org/derby/manuals/develop/develop63.html
	// ///////////////////////////////////////////////////////////////////////////////

	/*
	 * Helper function to create a default connection (to be called by Derby
	 * within Derby) for procedures
	 */
	private static Connection getDerbyProcedureConnection() throws SQLException {
		Connection conn = DriverManager
				.getConnection("jdbc:default:connection");
		conn.setAutoCommit(false); // all procedures will be transactional
		return conn;
	}

	/**
	 * Stored procedure to be registered in Derby database.
	 * 
	 * CREATE PROCEDURE duplicate_Scenario( IN sId CHAR(36), IN scId CHAR(36),
	 * OUT newSId(36) )
	 * 
	 * PARAMETERR STYLE JAVA LANGUAGE JAVA EXTERNAL NAME
	 * 'edu.cs.usask.ca.silver.persistence.datasource.DerbyStorage.duplicateScen
	 * a r i o ' ;
	 */
	public static void duplicateScenarioProcedure(String fromSIdString,
			String toSIdString, String toScIdString) throws SQLException {
		Connection conn = getDerbyProcedureConnection();

		UUID fromScenarioId = UUID.fromString(fromSIdString);
		UUID toSId = UUID.fromString(toSIdString);
		UUID toScId = UUID.fromString(toScIdString);
		UUID newVersion = UUID.randomUUID();

		// duplicate Scenario Parameter Set
		String sqlStmt = String.format(SCENARIO_SELECT_PSID, fromScenarioId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet fromPSSet = preparedStmt.executeQuery();
		int fromPSSetId = -1;
		while (fromPSSet.next()) {
			fromPSSetId = Integer.valueOf(fromPSSet.getInt(1));
		}

		// duplicate Parameter set
		sqlStmt = String.format(PARAMETERSET_COPY, fromPSSetId);
		preparedStmt = conn.prepareStatement(sqlStmt, Statement.RETURN_GENERATED_KEYS);
		preparedStmt.execute();
		ResultSet toPSSet = preparedStmt.getGeneratedKeys();
		int toPSSetId = -1;

		while (toPSSet.next()) {
			toPSSetId = Integer.valueOf(toPSSet.getInt(1));
		}

		// duplicate Parameter Set Scenarios
		sqlStmt = String.format(PARAMETERSET_SELECT_PARAMETERIDS,fromPSSetId);
		preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet fromParameters = preparedStmt.executeQuery();
		ResultSet temp;
		// Extract the ArrayList from the ResultSet
		while (fromParameters.next()) {
			// iterate through all parameters
			int fromParameterId = Integer.valueOf(fromParameters.getInt(1));
			sqlStmt = String.format(PARAMETER_COPY, fromParameterId,
					fromParameterId, fromParameterId, fromParameterId,
					fromParameterId);
			preparedStmt = conn.prepareStatement(sqlStmt,
					Statement.RETURN_GENERATED_KEYS);
			preparedStmt.execute();

			temp = preparedStmt.getGeneratedKeys();
			if (temp.next()) {
				sqlStmt = String.format(PARAMETER_ADD_PARAMETERTOPARAMETERSET,
						temp.getInt(1), toPSSetId, "", "");
				preparedStmt = conn.prepareStatement(sqlStmt);
				preparedStmt.execute();
			}
		}

		// duplicate scenario
		sqlStmt = String.format(SCENARIO_COPY, toSId, newVersion,
				fromScenarioId, fromScenarioId, fromScenarioId, fromScenarioId,
				fromScenarioId, fromScenarioId, fromScenarioId, fromScenarioId,
				toScId, toPSSetId);
		preparedStmt = conn.prepareStatement(sqlStmt);
		preparedStmt.execute();

		// duplicateScenarioTrajectoryFile
		sqlStmt = String.format(SCENARIO_SELECT_TRAJECTORYFILEIDS,
				fromScenarioId);
		preparedStmt = conn.prepareStatement(sqlStmt);
		temp = preparedStmt.executeQuery();
		while (temp.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(temp.getString(1)); // trajectory
																// file
																// that is to be
																// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			preparedStmt = conn.prepareStatement(sqlStmt,
					Statement.NO_GENERATED_KEYS);
			preparedStmt.execute();

			sqlStmt = String.format(TRAJECTORYFILE_INSERT, fileId, toSId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			preparedStmt.execute();
		}
		// duplicateScenarioAnalysisFile

		sqlStmt = String
				.format(SCENARIO_SELECT_ANALYSISFILEIDS, fromScenarioId);
		preparedStmt = conn.prepareStatement(sqlStmt);
		temp = preparedStmt.executeQuery();
		while (temp.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(temp.getString(1)); // trajectory
																// file
																// that is to be
																// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			preparedStmt = conn.prepareStatement(sqlStmt,
					Statement.NO_GENERATED_KEYS);
			preparedStmt.execute();

			sqlStmt = String.format(ANALYSISFILE_INSERT, fileId, toSId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			preparedStmt.execute();
		}

		// duplicateScenarioInputFile
		sqlStmt = String.format(SCENARIO_SELECT_INPUTFILEIDS, fromScenarioId);
		preparedStmt = conn.prepareStatement(sqlStmt);
		temp = preparedStmt.executeQuery();
		while (temp.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(temp.getString(1)); // trajectory
																// file
																// that is to be
																// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			preparedStmt = conn.prepareStatement(sqlStmt,
					Statement.NO_GENERATED_KEYS);
			preparedStmt.execute();

			sqlStmt = String.format(INPUTFILE_INSERT, fileId, toSId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			preparedStmt.execute();
		}

		// duplicateScenarioAssociatedFile
		sqlStmt = String
				.format(SCENARIO_SELECT_ANALYSISFILEIDS, fromScenarioId);
		preparedStmt = conn.prepareStatement(sqlStmt);
		temp = preparedStmt.executeQuery();
		while (temp.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(temp.getString(1)); // trajectory
																// file
																// that is to be
																// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			preparedStmt = conn.prepareStatement(sqlStmt,
					Statement.NO_GENERATED_KEYS);
			preparedStmt.execute();

			sqlStmt = String.format(ANALYSISFILE_INSERT, fileId, toSId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			preparedStmt.execute();
		}

		conn.close();
	}

	public static void duplicateScenarioCollectionProcedure(String fromScId,
			String toScId, String toMvId) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		UUID newversion = UUID.randomUUID();
		// duplicate scenario collection
		// new id, new version, old id, old id, old id, mv id
		String sqlStmt = String.format(SCOLLECTION_COPY, toScId, newversion, fromScId,fromScId,fromScId,toMvId);
		PreparedStatement ps = conn.prepareStatement(sqlStmt);
		ps.execute();

		// duplicate scenario collection associated files
		sqlStmt = String.format(SCOLLECTION_SELECT_ASSOCIATEDFILEIDS, fromScId);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt);
		ResultSet rs = ps1.executeQuery();
		while (rs.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(rs.getString(1)); // trajectory file
															// that is to be
															// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			ps1 = conn.prepareStatement(sqlStmt, Statement.NO_GENERATED_KEYS);
			ps1.execute();

			sqlStmt = String.format(FILE_ADDTO_SCENARIOCOLLECTION, fileId,
					toScId);
			ps1 = conn.prepareStatement(sqlStmt);
			ps1.execute();
		}

		// duplicate scenario collection scenarios
		sqlStmt = String.format(SCOLLECTION_SELECT_SCENARIOIDS, fromScId);
		PreparedStatement ps2 = conn.prepareStatement(sqlStmt);
		ResultSet rsScenario = ps2.executeQuery();
		PreparedStatement preparedStmt;
		ResultSet temp;
		while (rsScenario.next()) {
			UUID fromScenarioId = UUID.fromString(rsScenario.getString(1));
			UUID toSId = UUID.randomUUID();
			UUID newVersion = UUID.randomUUID();

			// duplicate Scenario Parameter Set
			sqlStmt = String.format(SCENARIO_SELECT_PSID, fromScenarioId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			ResultSet fromPSSet = preparedStmt.executeQuery();
			int fromPSSetId = -1;
			while (fromPSSet.next()) {
				fromPSSetId = Integer.valueOf(fromPSSet.getInt(1));
			}

			// duplicate Parameter set
			sqlStmt = String.format(PARAMETERSET_COPY, fromPSSetId);
			preparedStmt = conn.prepareStatement(sqlStmt, Statement.RETURN_GENERATED_KEYS);
			preparedStmt.execute();
			ResultSet toPSSet = preparedStmt.getGeneratedKeys();
			int toPSSetId = -1;

			while (toPSSet.next()) {
				toPSSetId = Integer.valueOf(toPSSet.getInt(1));
			}
			

			// duplicate Parameter Set Scenarios
			sqlStmt = String.format(PARAMETERSET_SELECT_PARAMETERIDS,fromPSSetId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			ResultSet fromParameters = preparedStmt.executeQuery();

			// Extract the ArrayList from the ResultSet
			while (fromParameters.next()) {
				// iterate through all parameters
				int fromParameterId = Integer.valueOf(fromParameters.getInt(1));
				sqlStmt = String.format(PARAMETER_COPY, fromParameterId,
						fromParameterId, fromParameterId, fromParameterId,
						fromParameterId);
				preparedStmt = conn.prepareStatement(sqlStmt,
						Statement.RETURN_GENERATED_KEYS);
				preparedStmt.execute();

				temp = preparedStmt.getGeneratedKeys();
				if (temp.next()) {
					sqlStmt = String.format(PARAMETER_ADD_PARAMETERTOPARAMETERSET,
							temp.getInt(1), toPSSetId, "", "");
					preparedStmt = conn.prepareStatement(sqlStmt);
					preparedStmt.execute();
				}
			}

			// duplicate scenario
			sqlStmt = String.format(SCENARIO_COPY, toSId, newVersion,
					fromScenarioId, fromScenarioId, fromScenarioId, fromScenarioId,
					fromScenarioId, fromScenarioId, fromScenarioId, fromScenarioId,
					toScId, toPSSetId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			preparedStmt.execute();

			// duplicateScenarioTrajectoryFile
			sqlStmt = String.format(SCENARIO_SELECT_TRAJECTORYFILEIDS,
					fromScenarioId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			temp = preparedStmt.executeQuery();
			while (temp.next()) {
				// iterate through all parameters
				UUID result = UUID.fromString(temp.getString(1)); // trajectory
																	// file
																	// that is to be
																	// copied
				UUID fileId = UUID.randomUUID();
				sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
						result);
				preparedStmt = conn.prepareStatement(sqlStmt,
						Statement.NO_GENERATED_KEYS);
				preparedStmt.execute();

				sqlStmt = String.format(TRAJECTORYFILE_INSERT, fileId, toSId);
				preparedStmt = conn.prepareStatement(sqlStmt);
				preparedStmt.execute();
			}
			// duplicateScenarioAnalysisFile

			sqlStmt = String
					.format(SCENARIO_SELECT_ANALYSISFILEIDS, fromScenarioId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			temp = preparedStmt.executeQuery();
			while (temp.next()) {
				// iterate through all parameters
				UUID result = UUID.fromString(temp.getString(1)); // trajectory
																	// file
																	// that is to be
																	// copied
				UUID fileId = UUID.randomUUID();
				sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
						result);
				preparedStmt = conn.prepareStatement(sqlStmt,
						Statement.NO_GENERATED_KEYS);
				preparedStmt.execute();

				sqlStmt = String.format(ANALYSISFILE_INSERT, fileId, toSId);
				preparedStmt = conn.prepareStatement(sqlStmt);
				preparedStmt.execute();
			}

			// duplicateScenarioInputFile
			sqlStmt = String.format(SCENARIO_SELECT_INPUTFILEIDS, fromScenarioId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			temp = preparedStmt.executeQuery();
			while (temp.next()) {
				// iterate through all parameters
				UUID result = UUID.fromString(temp.getString(1)); // trajectory
																	// file
																	// that is to be
																	// copied
				UUID fileId = UUID.randomUUID();
				sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
						result);
				preparedStmt = conn.prepareStatement(sqlStmt,
						Statement.NO_GENERATED_KEYS);
				preparedStmt.execute();

				sqlStmt = String.format(INPUTFILE_INSERT, fileId, toSId);
				preparedStmt = conn.prepareStatement(sqlStmt);
				preparedStmt.execute();
			}

			// duplicateScenarioAssociatedFile
			sqlStmt = String
					.format(SCENARIO_SELECT_ANALYSISFILEIDS, fromScenarioId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			temp = preparedStmt.executeQuery();
			while (temp.next()) {
				// iterate through all parameters
				UUID result = UUID.fromString(temp.getString(1)); // trajectory
																	// file
																	// that is to be
																	// copied
				UUID fileId = UUID.randomUUID();
				sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
						result);
				preparedStmt = conn.prepareStatement(sqlStmt,
						Statement.NO_GENERATED_KEYS);
				preparedStmt.execute();

				sqlStmt = String.format(ANALYSISFILE_INSERT, fileId, toSId);
				preparedStmt = conn.prepareStatement(sqlStmt);
				preparedStmt.execute();
			}
		}

		conn.close();
	}


	/**
	 * A Derby procedure procedure to duplicate parameter row
	 * 
	 * @param pId
	 * @param data1
	 * @throws SQLException
	 */
	public static void duplicateParameterProcedure(int pId, ResultSet[] data1)
			throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		String sqlStmt = String.format(PARAMETER_COPY, pId, pId, pId, pId, pId);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt,
				Statement.RETURN_GENERATED_KEYS);
		ps1.execute();
		data1[0] = ps1.getGeneratedKeys();
		conn.close();
	}

	/**
	 * A Derby procedure to duplicate trajectory
	 * 
	 * @param fromSId
	 * @param toSId
	 * @throws SQLException
	 */
	public static void duplicateScenarioTrajectoryFilesProcedure(
			String fromSId, String toSId) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		String sqlStmt = String.format(SCENARIO_SELECT_TRAJECTORYFILEIDS,
				fromSId);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt);
		ResultSet rs = ps1.executeQuery();
		while (rs.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(rs.getString(1)); // trajectory file
															// that is to be
															// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			ps1 = conn.prepareStatement(sqlStmt, Statement.NO_GENERATED_KEYS);
			ps1.execute();

			sqlStmt = String.format(TRAJECTORYFILE_INSERT, fileId, toSId);
			ps1 = conn.prepareStatement(sqlStmt);
			ps1.execute();
		}

		conn.close();
	}

	/**
	 * A Derby procedure to duplicate input file
	 * 
	 * @param fromSId
	 * @param toSId
	 * @throws Exception
	 */
	public static void duplicateScenarioInputFilesProcedure(String fromSId,
			String toSId) throws Exception {
		Connection conn = getDerbyProcedureConnection();
		String sqlStmt = String.format(SCENARIO_SELECT_INPUTFILEIDS, fromSId);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt);
		ResultSet rs = ps1.executeQuery();
		while (rs.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(rs.getString(1)); // trajectory file
															// that is to be
															// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			ps1 = conn.prepareStatement(sqlStmt, Statement.NO_GENERATED_KEYS);
			ps1.execute();

			sqlStmt = String.format(INPUTFILE_INSERT, fileId, toSId);
			ps1 = conn.prepareStatement(sqlStmt);
			ps1.execute();
		}

		conn.close();
	}

	public static void duplicateScenarioAnalysisFilesProcedure(String fromSId,
			String toSId) throws Exception {
		Connection conn = getDerbyProcedureConnection();
		String sqlStmt = String
				.format(SCENARIO_SELECT_ANALYSISFILEIDS, fromSId);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt);
		ResultSet rs = ps1.executeQuery();
		while (rs.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(rs.getString(1)); // trajectory file
															// that is to be
															// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			ps1 = conn.prepareStatement(sqlStmt, Statement.NO_GENERATED_KEYS);
			ps1.execute();

			sqlStmt = String.format(ANALYSISFILE_INSERT, fileId, toSId);
			ps1 = conn.prepareStatement(sqlStmt);
			ps1.execute();
		}

		conn.close();
	}

	/**
	 * A remote procedure to duplicate parameters between two parameter sets
	 * 
	 * @param psIdFrom
	 *            - parameter set id to copy from
	 * @param psIdTo
	 *            - parameter set id to copy to
	 * @throws SQLException
	 */
	public static void duplicatePramameterSetParametersProcedure(int psIdFrom,
			int psIdTo) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		String sqlStmt = String.format(PARAMETERSET_SELECT_PARAMETERIDS,
				psIdFrom);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt);
		ResultSet rs = ps1.executeQuery();
		// Extract the ArrayList from the ResultSet
		ResultSet temp;
		while (rs.next()) {
			// iterate through all parameters
			int result = Integer.valueOf(rs.getInt(1));
			sqlStmt = String.format(PARAMETER_COPY, result, result, result,
					result, result);
			ps1 = conn.prepareStatement(sqlStmt,
					Statement.RETURN_GENERATED_KEYS);
			ps1.execute();
			temp = ps1.getGeneratedKeys();
			if (temp.next()) {
				sqlStmt = String.format(PARAMETER_ADD_PARAMETERTOPARAMETERSET,
						temp.getInt(1), psIdTo, "", "");
				ps1 = conn.prepareStatement(sqlStmt);
				ps1.execute();
			}
		}
		conn.close();
	}

	/**
	 * A Derby procedure to duplicate trajectory
	 * 
	 * @param fromSId
	 * @param toSId
	 * @throws SQLException
	 */
	public static void duplicateScenarioAssociateFilesProcedure(String fromSId,
			String toSId) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		String sqlStmt = String.format(SCENARIO_SELECT_ASSOCIATEDFILEIDS,
				fromSId);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt);
		ResultSet rs = ps1.executeQuery();
		while (rs.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(rs.getString(1)); // trajectory file
															// that is to be
															// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			ps1 = conn.prepareStatement(sqlStmt, Statement.NO_GENERATED_KEYS);
			ps1.execute();

			sqlStmt = String.format(FILE_ADDTO_SCENARIO, fileId, toSId);
			ps1 = conn.prepareStatement(sqlStmt);
			ps1.execute();
		}

		conn.close();
	}
	public static void getXMLRepresentationForUserProcedure(String fromUser,
			String[] userXMLRepresentation) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		try {
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document returnDoc = builder.newDocument();

			userXMLRepresentation[0] = buildXMLReportForUserProcedure(null,
					fromUser, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		conn.close();
	}

	public static void getXMLRepresentationForGroupProcedure(String fromGroup,
			String[] groupXMLRepresentation) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		try {
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document returnDoc = builder.newDocument();

			groupXMLRepresentation[0] = buildXMLReportForGroupProcedure(null,
					fromGroup, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		conn.close();
	}

	public static void getXMLRepresentationForModelProjectProcedure(
			String fromModelProject, String[] modelprojectXMLRepresentation)
			throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		try {
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document returnDoc = builder.newDocument();

			modelprojectXMLRepresentation[0] = buildXMLReportForModelProjectProcedure(
					null, fromModelProject, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		conn.close();
	}

	public static void getXMLRepresentationForModelVersionProcedure(
			String fromModelVersion, String[] modelversionXMLRepresentation)
			throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		try {
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document returnDoc = builder.newDocument();

			modelversionXMLRepresentation[0] = buildXMLReportForModelVersionProcedure(
					null, fromModelVersion, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		conn.close();
	}

	public static void getXMLRepresentationForScenarioCollectionProcedure(
			String fromScenarioCollection,
			String[] scenarioCollectionXMLRepresentation) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		try {
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document returnDoc = builder.newDocument();

			scenarioCollectionXMLRepresentation[0] = buildXMLReportForScenarioCollectionProcedure(
					null, fromScenarioCollection, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		conn.close();
	}

	public static void getXMLRepresentationForScenarioProcedure(
			String fromScenarioId, String[] scenarioXMLRepresentation)
			throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		try {
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document returnDoc = builder.newDocument();

			scenarioXMLRepresentation[0] = buildXMLReportForScenarioProcedure(
					null, fromScenarioId, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		conn.close();
	}

	private static String buildXMLReportForUserProcedure(Element parent,
			String fromUserId, Connection conn, DocumentBuilder builder,
			Document returnDoc) throws Exception {
		// represent parameter sets
		String sqlStmt = String.format(USER_SELECT_PARTICIPATED_GROUP_IDS,
				fromUserId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet rs = null;
		String result = null;
		rs = preparedStmt.executeQuery();
		try {
			result = generateXMLRepresentationOfResultSet(parent, rs, conn,
					builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}

		return result;
	}

	private static String buildXMLReportForGroupProcedure(Element parent,
			String fromGroupId, Connection conn, DocumentBuilder builder,
			Document returnDoc) throws Exception {
		// represent parameter sets
		String sqlStmt = String.format(GROUP_SELECT_MPROJECT_ID_AND_NAME,
				fromGroupId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet rs = null;
		rs = preparedStmt.executeQuery();
		String result = null;
		try {
			result = generateXMLRepresentationOfResultSet(parent, rs, conn,
					builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}

		return result;
	}

	private static String buildXMLReportForModelProjectProcedure(
			Element parent, String fromModelProjectId, Connection conn,
			DocumentBuilder builder, Document returnDoc) throws Exception {
		// represent parameter sets
		String sqlStmt = String.format(MODEL_PROJECT_SELECT_MVERSIONIDANDNAME,
				fromModelProjectId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet rs = null;
		rs = preparedStmt.executeQuery();
		String result = null;
		try {
			result = generateXMLRepresentationOfResultSet(parent, rs, conn,
					builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}

		return result;
	}

	private static String buildXMLReportForModelVersionProcedure(
			Element parent, String fromModelVersionId, Connection conn,
			DocumentBuilder builder, Document returnDoc) throws Exception {
		// represent parameter sets
		String sqlStmt = String.format(MODEL_VERSION_SELECT_SCIDANDNAME,
				fromModelVersionId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet rs = null;
		rs = preparedStmt.executeQuery();
		String result = null;
		try {
			result = generateXMLRepresentationOfResultSet(parent, rs, conn,
					builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}

		return result;

	}

	private static String buildXMLReportForScenarioCollectionProcedure(
			Element parent, String fromScenarioCollectionId, Connection conn,
			DocumentBuilder builder, Document returnDoc) throws Exception {

		// represent parameter sets
		String sqlStmt = String.format(SCOLLECTION_SELECT_SCENARIOIDSANDNAMES,
				fromScenarioCollectionId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet rs = null;
		rs = preparedStmt.executeQuery();
		String result = null;
		try {
			result = generateXMLRepresentationOfResultSet(parent, rs, conn,
					builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}

		return result;
	}

	private static String buildXMLReportForScenarioProcedure(Element parent,
			String fromScenarioId, Connection conn, DocumentBuilder builder,
			Document returnDoc) throws Exception {
		// represent parameter sets
		String sqlStmt = String.format(SCENARIO_SELECT_PSID, fromScenarioId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet fromPSSet = preparedStmt.executeQuery();
		ResultSet rs = null;
		int fromPSSetId = -1;
		while (fromPSSet.next()) {
			fromPSSetId = Integer.valueOf(fromPSSet.getInt(1));
		}
		sqlStmt = String.format(PARAMETERSET_SELECT_PARAMETERIDS, fromPSSetId);
		preparedStmt = conn.prepareStatement(sqlStmt);

		rs = preparedStmt.executeQuery();
		String result = null;
		try {
			result = generateXMLRepresentationOfResultSet(parent, rs, conn,
					builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		return result;
	}

	private static String generateXMLRepresentationOfResultSet(Element parent,
			ResultSet rs, Connection conn, DocumentBuilder builder,
			Document returnDoc) throws Exception {

		// we need to supply a name space to apply with xpath
		// Element results = returnDoc.createElement("SILVERCOMPONENT");
		if (parent == null) {
			// ////// turn results set into DOM
			parent = returnDoc.createElementNS("silver", "SILVERCOMPONENT");
			returnDoc.appendChild(parent);
		}

		ResultSetMetaData rsmd = rs.getMetaData();
		int colCount = rsmd.getColumnCount();
		Element row;
		String tablename;

		String columnName;
		UUID id = null;
		Object value;
		Element node = null;
		while (rs.next()) {
			tablename = rsmd.getTableName(1);
			row = returnDoc.createElement(tablename);

			parent.appendChild(row);

			for (int i = 1; i <= colCount; i++) {
				if (i == 1 && tablename.compareToIgnoreCase("P2PS") != 0)
					id = UUID.fromString(rs.getObject(i).toString());

				columnName = rsmd.getColumnName(i);
				value = rs.getObject(i);
				node = returnDoc.createElement(columnName);
				node.appendChild(returnDoc.createTextNode(value.toString()));
				row.appendChild(node);
			}
			// node = createElement(child node)
			if (tablename.compareToIgnoreCase("P2PS") != 0)
				evaluateNodeAndPrintChildren(tablename, row, id, conn, builder,
						returnDoc);

		}

		// ///////////////
		// Generate the XML

		// set up a transformer
		TransformerFactory transfac = TransformerFactory.newInstance();
		Transformer trans = transfac.newTransformer();
		trans.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
		trans.setOutputProperty(OutputKeys.INDENT, "yes");

		// create string from xml tree
		StringWriter sw = new StringWriter();
		StreamResult result = new StreamResult(sw);
		DOMSource source = new DOMSource(returnDoc);
		trans.transform(source, result);
		return sw.toString();
	}

	private static void evaluateNodeAndPrintChildren(String tablename,
			Element parent, UUID id, Connection conn, DocumentBuilder builder,
			Document returnDoc) throws Exception {
		if (tablename.equalsIgnoreCase("U2GROUP")) {
			buildXMLReportForGroupProcedure(parent, id.toString(), conn,
					builder, returnDoc);
		} else if (tablename.equalsIgnoreCase("MPROJECT")) {
			buildXMLReportForModelProjectProcedure(parent, id.toString(), conn,
					builder, returnDoc);
		} else if (tablename.equalsIgnoreCase("MVERSION")) {
			buildXMLReportForModelVersionProcedure(parent, id.toString(), conn,
					builder, returnDoc);
		} else if (tablename.equalsIgnoreCase("SCENARIOCOLLECTION")) {
			buildXMLReportForScenarioCollectionProcedure(parent, id.toString(),
					conn, builder, returnDoc);
		} else if (tablename.equalsIgnoreCase("SCENARIO")) {
			buildXMLReportForScenarioProcedure(parent, id.toString(), conn,
					builder, returnDoc);
		} else {
			return;
		}

	}


}
